﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using IDAL;

namespace SQLServer
{
    public class DatabaseInfo : IDatabaseInfo
    {
        private string _connString = "";
        private string _Database;
        private string _ServerName = ".";
        private bool _IsWindow = true;
        private string _UID;
        private string _PWD;

        public string ServerName{
            get { return _ServerName; }
            set { _ServerName = value; _connString = GetConnStr(); }
        }

        public bool IsWindow
        {
            get { return IsWindow; }
            set {
                _IsWindow = value;
                _connString = GetConnStr();
            }        
        }

        public string UID
        {
            get { return _UID; }
            set {
                _UID = value;
                _connString = GetConnStr();
            }
        }

        public string PWD
        {
            get { return _PWD; }
            set {
                _PWD = value;
                _connString = GetConnStr();
            }
        }

        public string DatabaseName {
            get { return _Database; }
            set {
                _Database = value;
                _connString = GetConnStr();
            }
        }

        public DatabaseInfo()
        {
 
        }

        public DatabaseInfo(string ServerName, bool IsWindow, string UID, string PWD)
        {
            _ServerName = ServerName;
            _UID = UID;
            _PWD = PWD;
            _IsWindow = IsWindow;
            _Database = "master";
            _connString = GetConnStr();            
        }

        public DatabaseInfo(string ServerName, bool IsWindow, string UID, string PWD, string Database)
        {
            _ServerName = ServerName;
            _UID = UID;
            _PWD = PWD;
            _IsWindow = IsWindow;
            _Database = Database;
            _connString = GetConnStr();
        }

        private string GetConnStr()
        {
            return GetConnStr(_Database);
        }

        private string GetConnStr(string Database)
        {
            string LoginWord = _IsWindow ? "Integrated Security=SSPI" : "uid=" + _UID + ";pwd=" + _PWD;
            return @"server=" + _ServerName + ";database=" + _Database + ";" + LoginWord;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool ConnServer()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_connString))
                {
                    conn.Open();
                    conn.Close();
                }
                return true;
            }
            catch (Exception E)
            {
                return false;
            }
        }

        /// <summary>
        /// 取得数据库列表
        /// </summary>
        /// <returns></returns>
        public List<DatabaseModel> GetDatabaseList()
        {
            List<DatabaseModel> dl = new List<DatabaseModel>();
            using (SqlConnection conn = new SqlConnection(GetConnStr("master")))
            {
                SqlCommand com = new SqlCommand("select * from sysdatabases", conn);
                SqlDataAdapter sda = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DatabaseModel dm = new DatabaseModel();
                    dm.DatabaseName = dt.Rows[i][0].ToString();
                    dl.Add(dm);                    
                }
            }
            return dl;
        }

        /// <summary>
        /// 取得表列表
        /// </summary>
        /// <returns></returns>
        public List<TableModel> GetTableList()
        {
            List<TableModel> dl = new List<TableModel>();
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                SqlCommand com = new SqlCommand("select * from sysobjects where xtype='U' and [name]<>'dtproperties'", conn);
                SqlDataAdapter sda = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    TableModel dm = new TableModel();
                    dm.TableName = dt.Rows[i]["name"].ToString();
                    dl.Add(dm);
                }                
            }
            return dl;
        }

        /// <summary>
        /// 取得字段
        /// </summary>
        /// <param name="Table"></param>
        /// <returns></returns>
        public List<FieldModel> GetFieldList(string Table)
        {

            List<FieldModel> dl = new List<FieldModel>();
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendLine("SELECT");
                strSql.AppendLine("表名=case   when   a.colorder=1   then   d.name   else   ''   end,");
                strSql.AppendLine("表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,");
                strSql.AppendLine("字段序号=a.colorder,");
                strSql.AppendLine("字段名=a.name,");
                strSql.AppendLine("标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√' else   ''   end,");
                strSql.AppendLine("主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (");
                strSql.AppendLine("SELECT   name   FROM   sysindexes   WHERE   indid   in(");
                strSql.AppendLine("SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid");
                strSql.AppendLine(")))   then   '√'   else   ''   end,");
                strSql.AppendLine("类型=b.name,");
                strSql.AppendLine("占用字节数=a.length,");
                strSql.AppendLine("长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),");
                strSql.AppendLine("小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),");
                strSql.AppendLine("允许空=case   when   a.isnullable=1   then   '√'else   ''   end,");
                strSql.AppendLine("默认值=isnull(e.text,''),");
                strSql.AppendLine("字段说明=isnull(g.[value],'')");
                strSql.AppendLine("FROM   syscolumns   a");
                strSql.AppendLine("left   join   systypes   b   on   a.xusertype=b.xusertype");
                strSql.AppendLine("inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'");
                strSql.AppendLine("left   join   syscomments   e   on   a.cdefault=e.id");
                strSql.AppendLine("left   join   sys.extended_properties   g   on   a.id=g.major_id   and   a.colid=g.minor_id");
                strSql.AppendLine("left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0");
                strSql.AppendLine(string.Format("where d.name='{0}'", Table));         //如果只查询指定表,加上此条件
                strSql.AppendLine("order   by   a.id,a.colorder");

                conn.Open();
                
                SqlCommand com = new SqlCommand(strSql.ToString(), conn);

                SqlDataReader dr = com.ExecuteReader();

                while (dr.Read())
                {
                    FieldModel dm = new FieldModel();
                    dm.FieldName = (string)dr["字段名"];
                    dm.TypeName = (string)dr["类型"];
                    dm.CanEmpty = (((string)dr["允许空"]).Equals("√"));
                    dm.Description = (string)dr["字段说明"];
                    dl.Add(dm);
                }

                dr.Close();

                conn.Close();

                /*
                SqlCommand com = new SqlCommand("select top 1 from [" + Table + "]", conn);
                SqlDataAdapter sda = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                foreach (DataColumn column in dt.Columns)
                {
                    FieldModel dm = new FieldModel();
                    dm.FieldName = column.ColumnName;
                    dm.TypeName = column.DataType.Name;
                    dm.Description = column.Caption;
                    dl.Add(dm);
                }*/

            }
            return dl;
        }
    }
}
